"""
Excel 格式导入导出策略
"""
from __future__ import annotations

from datetime import datetime
from pathlib import Path
from typing import Any, Dict, List, Tuple

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter

from ..domain import (
    ProjectSnapshot,
    ProjectMetadata,
    CategorySnapshot,
    ParameterSnapshot,
    ParameterValueSnapshot,
)
from .base import ExportStrategy, ImportStrategy, ExportResult


class ExcelExportStrategy(ExportStrategy):
    """将项目快照导出为 Excel 文件"""

    @classmethod
    def get_format_info(cls) -> Dict[str, Any]:
        """获取 Excel 导出格式信息"""
        return {
            "format": "excel",
            "name": "富格式Excel文件",
            "extension": ".xlsx",
            "description": "多sheet、超链接、隐藏元数据的Excel文件"
        }

    def __init__(self, output_dir: Path = Path("exports")) -> None:
        self.output_dir = output_dir
        self.font_normal = Font(name="微软雅黑", size=10)
        self.font_small_italic_gray = Font(name="微软雅黑", size=9, italic=True, color="666666")
        self.header_font_dark = Font(name="微软雅黑", size=11, bold=True, color="FFFFFF")
        self.header_fill_blue = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
        self.header_fill_green = PatternFill(start_color="70AD47", end_color="70AD47", fill_type="solid")
        self.header_fill_teal = PatternFill(start_color="5B9BD5", end_color="5B9BD5", fill_type="solid")
        self.title_font = Font(name="微软雅黑", size=14, bold=True, color="FFFFFF")
        self.title_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
        self.center = Alignment(horizontal="center", vertical="center")
        self.left = Alignment(horizontal="left", vertical="center")
        self.thin_border = Border(
            left=Side(style='thin'), right=Side(style='thin'),
            top=Side(style='thin'), bottom=Side(style='thin')
        )

    def export(self, snapshot: ProjectSnapshot) -> ExportResult:
        snapshot.ensure_export_time()
        self.output_dir.mkdir(parents=True, exist_ok=True)
        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        filename = f"{snapshot.metadata.name_en}_parameters_rich_{timestamp}.xlsx"
        file_path = self.output_dir / filename

        wb = Workbook()
        wb.remove(wb.active)

        # 创建各个工作表
        self._sheet_project_overview(wb, snapshot)
        catalog_ws = wb.create_sheet("参数目录", 1)
        category_sheet_refs: Dict[Tuple[str, str], Tuple[str, int]] = {}
        
        for cat in snapshot.categories:
            sheet_name = self._safe_sheet_name(cat.name)
            ws = wb.create_sheet(sheet_name)
            self._sheet_category_definition(ws, cat, category_sheet_refs)

        values_single_refs: Dict[Tuple[str, str], int] = {}
        self._sheet_values_single(wb.create_sheet("值_单值"), snapshot, values_single_refs)
        
        values_year_refs: Dict[Tuple[str, str], int] = {}
        self._sheet_values_list_year(wb.create_sheet("值_列表_年份"), snapshot, values_year_refs)
        
        values_noyear_refs: Dict[Tuple[str, str], int] = {}
        self._sheet_values_list_noyear(wb.create_sheet("值_列表_非年份"), snapshot, values_noyear_refs)
        
        self._sheet_catalog(catalog_ws, snapshot, category_sheet_refs)
        self._update_param_definition_links(wb, snapshot, values_single_refs, values_year_refs, values_noyear_refs)
        
        wb.save(file_path)
        return ExportResult(
            file_path=file_path,
            mime_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        )

    def get_diff_ignore_fields(self) -> List[str]:
        """Excel 格式需要屏蔽的字段：sort_order 和 list_length（Excel 文件中不包含这些字段）"""
        return ["category.sort_order", "parameter.sort_order", "parameter.list_length"]

    def _sheet_project_overview(self, wb: Workbook, snapshot: ProjectSnapshot):
        ws = wb.create_sheet("项目概览", 0)
        ws.merge_cells('A1:D1')
        ws['A1'] = f"项目参数导出 - {snapshot.metadata.name}"
        ws['A1'].font = self.title_font
        ws['A1'].fill = self.title_fill
        ws['A1'].alignment = self.center
        
        info = [
            ["项目名称", snapshot.metadata.name],
            ["项目英文名", snapshot.metadata.name_en],
            ["项目描述", snapshot.metadata.description or "无"],
            ["时间长度", f"{snapshot.metadata.time_horizon} 年"],
            ["起始年份", snapshot.metadata.start_year],
            ["年份步长", snapshot.metadata.year_step],
            ["结束年份", snapshot.metadata.end_year],
            ["导出时间", snapshot.metadata.export_time.strftime("%Y-%m-%d %H:%M:%S") if snapshot.metadata.export_time else datetime.now().strftime("%Y-%m-%d %H:%M:%S")],
        ]
        row = 3
        for label, val in info:
            ws[f'A{row}'] = label
            ws[f'B{row}'] = val
            ws[f'A{row}'].font = Font(name="微软雅黑", size=11, bold=True)
            ws[f'B{row}'].font = Font(name="微软雅黑", size=11)
            row += 1
        
        ws[f'A{row+1}'] = "分类名称"
        ws[f'B{row+1}'] = "分类英文名"
        ws[f'C{row+1}'] = "参数数量"
        ws[f'D{row+1}'] = "描述"
        for col in ['A', 'B', 'C', 'D']:
            cell = ws[f'{col}{row+1}']
            cell.font = self.header_font_dark
            cell.fill = self.header_fill_blue
            cell.alignment = self.center
            cell.border = self.thin_border
        
        r = row + 2
        for cat in snapshot.categories:
            cat_name_cell = ws[f'A{r}']
            cat_name_cell.value = cat.name
            cat_name_cell.font = Font(name="微软雅黑", size=11, color="0563C1", underline="single")
            cat_sheet_name = self._safe_sheet_name(cat.name)
            cat_name_cell.hyperlink = f"#{cat_sheet_name}!A1"
            cat_name_cell.style = "Hyperlink"
            
            ws[f'B{r}'] = cat.name_en
            ws[f'C{r}'] = len(cat.parameters)
            ws[f'D{r}'] = cat.description or "无"
            for col in ['A', 'B', 'C', 'D']:
                ws[f'{col}{r}'].border = self.thin_border
            ws[f'C{r}'].alignment = self.center
            r += 1
        
        ws.column_dimensions['A'].width = 20
        ws.column_dimensions['B'].width = 25
        ws.column_dimensions['C'].width = 12
        ws.column_dimensions['D'].width = 30

    def _sheet_catalog(self, ws, snapshot: ProjectSnapshot, refs: Dict[Tuple[str, str], Tuple[str, int]]):
        headers = ["分类", "分类英文名", "参数名称", "参数英文名", "描述"]
        for i, h in enumerate(headers, 1):
            c = ws.cell(row=1, column=i, value=h)
            c.font = self.header_font_dark
            c.fill = self.header_fill_green
            c.alignment = self.center
            c.border = self.thin_border
        
        row = 2
        for cat in snapshot.categories:
            params_sorted = sorted(cat.parameters, key=lambda p: (-p.sort_order, p.name or '', p.name_en or ''))
            if params_sorted:
                start_row = row
                end_row = row + len(params_sorted) - 1
                if start_row == end_row:
                    cat_cell = ws.cell(row=row, column=1, value=cat.name)
                    cat_cell.font = self.font_normal
                    cat_cell.alignment = Alignment(horizontal="center", vertical="center")
                    cat_en_cell = ws.cell(row=row, column=2, value=cat.name_en)
                    cat_en_cell.font = self.font_normal
                    cat_en_cell.alignment = Alignment(horizontal="center", vertical="center")
                else:
                    ws.merge_cells(f'A{start_row}:A{end_row}')
                    ws.merge_cells(f'B{start_row}:B{end_row}')
                    cat_cell = ws[f'A{start_row}']
                    cat_cell.value = cat.name
                    cat_cell.font = self.font_normal
                    cat_cell.alignment = Alignment(horizontal="center", vertical="center")
                    cat_cell.border = Border(
                        left=Side(style='thin'), right=Side(style='thin'),
                        top=Side(style='thin'), bottom=Side(style='thin')
                    )
                    cat_en_cell = ws[f'B{start_row}']
                    cat_en_cell.value = cat.name_en
                    cat_en_cell.font = self.font_normal
                    cat_en_cell.alignment = Alignment(horizontal="center", vertical="center")
                    cat_en_cell.border = Border(
                        left=Side(style='thin'), right=Side(style='thin'),
                        top=Side(style='thin'), bottom=Side(style='thin')
                    )
                    for r in range(start_row + 1, end_row + 1):
                        for col in range(1, 6):
                            ws.cell(row=r, column=col).border = Border(
                                left=Side(style='thin'), right=Side(style='thin')
                            )
                
                for p in params_sorted:
                    name_cell = ws.cell(row=row, column=3, value=p.name)
                    name_cell.font = Font(name="微软雅黑", size=10, color="0563C1", underline="single")
                    key = (cat.name_en, p.name_en)
                    if key in refs:
                        sheet_name, target_row = refs[key]
                        name_cell.hyperlink = f"#{sheet_name}!A{target_row}"
                        name_cell.style = "Hyperlink"
                    ws.cell(row=row, column=4, value=p.name_en).font = self.font_normal
                    ws.cell(row=row, column=5, value=p.description or "").font = self.font_normal
                    for col in range(1, 6):
                        ws.cell(row=row, column=col).border = self.thin_border
                    row += 1
                
                if params_sorted:
                    last_row = row - 1
                    for col in range(1, 6):
                        cell = ws.cell(row=last_row, column=col)
                        cb = cell.border
                        cell.border = Border(
                            left=cb.left, right=cb.right, top=cb.top,
                            bottom=Side(style='thin')
                        )
        
        widths = [16, 20, 18, 22, 40]
        for idx, w in enumerate(widths, 1):
            ws.column_dimensions[get_column_letter(idx)].width = w

    def _sheet_category_definition(self, ws, category: CategorySnapshot, refs: Dict[Tuple[str, str], Tuple[str, int]]):
        ws.merge_cells('A1:G1')
        ws['A1'] = f"{category.name} ({category.name_en})"
        ws['A1'].font = self.title_font
        ws['A1'].fill = self.title_fill
        ws['A1'].alignment = self.center
        
        info_row = 2
        if category.description:
            ws.merge_cells('A2:G2')
            ws['A2'] = f"描述: {category.description}"
            ws['A2'].font = Font(name="微软雅黑", size=10, italic=True)
            ws['A2'].alignment = self.left
            info_row = 3
        
        header_row = info_row + 1
        headers = ["参数名称", "参数英文名", "参数类型", "单位", "是否列表", "是否关联年份", "描述"]
        for i, h in enumerate(headers, 1):
            c = ws.cell(row=header_row, column=i, value=h)
            c.font = self.header_font_dark
            c.fill = self.header_fill_green
            c.alignment = self.center
            c.border = self.thin_border
        
        row = header_row + 1
        for p in sorted(category.parameters, key=lambda p: (-p.sort_order, p.name or '')):
            name_cell = ws.cell(row=row, column=1, value=p.name)
            name_cell.font = Font(name="微软雅黑", size=10, color="0563C1", underline="single")
            ws.cell(row=row, column=2, value=p.name_en).font = self.font_normal
            ws.cell(row=row, column=3, value=p.param_type).font = self.font_normal
            ws.cell(row=row, column=4, value=p.unit or "").font = self.font_normal
            ws.cell(row=row, column=5, value="是" if p.is_list else "否").font = self.font_normal
            ws.cell(row=row, column=6, value="是" if p.is_year_related else "否").font = self.font_normal
            ws.cell(row=row, column=7, value=p.description or "").font = self.font_normal
            refs[(category.name_en, p.name_en)] = (ws.title, row)
            for col in range(1, 8):
                ws.cell(row=row, column=col).border = self.thin_border
            row += 1
        
        widths = [16, 20, 12, 10, 10, 12, 40]
        for idx, w in enumerate(widths, 1):
            ws.column_dimensions[get_column_letter(idx)].width = w

    def _append_category_separator(self, ws, row: int, title: str, max_col: int = None) -> int:
        if max_col is None:
            max_col = ws.max_column if ws.max_column > 0 else 8
        end_col = get_column_letter(max_col)
        ws.merge_cells(f'A{row}:{end_col}{row}')
        c = ws[f'A{row}']
        c.value = f"【{title}】"
        c.font = Font(name="微软雅黑", size=12, bold=True, color="FFFFFF")
        c.fill = PatternFill(start_color="FF6B6B", end_color="FF6B6B", fill_type="solid")
        c.alignment = self.center
        return row + 1

    def _sheet_values_single(self, ws, snapshot: ProjectSnapshot, refs: Dict[Tuple[str, str], int] = None):
        headers = ["参数名称", "参数英文名", "值", "单位"]
        for i, h in enumerate(headers, 1):
            c = ws.cell(row=1, column=i, value=h)
            c.font = self.header_font_dark
            c.fill = self.header_fill_blue
            c.alignment = self.center
            c.border = self.thin_border
        
        row = 2
        for cat in snapshot.categories:
            has_single_params = any(not p.is_list for p in cat.parameters)
            if not has_single_params:
                continue
            
            row = self._append_category_separator(ws, row, f"{cat.name} / {cat.name_en}", 4)
            params = sorted(cat.parameters, key=lambda p: (-p.sort_order, p.name or ''))
            for p in params:
                if p.is_list:
                    continue
                if refs is not None:
                    refs[(cat.name_en, p.name_en)] = row
                ws.cell(row=row, column=1, value=p.name).font = self.font_normal
                ws.cell(row=row, column=2, value=p.name_en).font = self.font_normal
                val = p.values[0].value if p.values and p.values[0].value is not None else None
                ws.cell(row=row, column=3, value=("" if val is None else str(val))).font = self.font_normal
                ws.cell(row=row, column=4, value=p.unit or "").font = self.font_normal
                for col in range(1, 5):
                    ws.cell(row=row, column=col).border = self.thin_border
                row += 1
        
        widths = [18, 22, 16, 10]
        for idx, w in enumerate(widths, 1):
            ws.column_dimensions[get_column_letter(idx)].width = w

    def _sheet_values_list_year(self, ws, snapshot: ProjectSnapshot, refs: Dict[Tuple[str, str], int] = None):
        years = [
            snapshot.metadata.start_year + i * snapshot.metadata.year_step
            for i in range(snapshot.metadata.time_horizon)
        ]
        headers = ["参数名称", "参数英文名", *years, "单位"]
        for i, h in enumerate(headers, 1):
            c = ws.cell(row=1, column=i, value=h)
            c.font = self.header_font_dark
            c.fill = self.header_fill_teal
            c.alignment = self.center
            c.border = self.thin_border
        
        row = 2
        for cat in snapshot.categories:
            has_year_list_params = any(p.is_list and p.is_year_related for p in cat.parameters)
            if not has_year_list_params:
                continue
            
            row = self._append_category_separator(ws, row, f"{cat.name} / {cat.name_en}", len(years) + 3)
            params = sorted(cat.parameters, key=lambda p: (-p.sort_order, p.name or ''))
            for p in params:
                if not (p.is_list and p.is_year_related):
                    continue
                if refs is not None:
                    refs[(cat.name_en, p.name_en)] = row
                ws.cell(row=row, column=1, value=p.name).font = self.font_normal
                ws.cell(row=row, column=2, value=p.name_en).font = self.font_normal
                for i, y in enumerate(years):
                    val = p.values[i].value if i < len(p.values) else None
                    ws.cell(row=row, column=3 + i, value=(None if val in (None, "") else val)).font = self.font_normal
                ws.cell(row=row, column=3 + len(years), value=p.unit or "").font = self.font_normal
                for col in range(1, 4 + len(years)):
                    ws.cell(row=row, column=col).border = self.thin_border
                row += 1
        
        ws.column_dimensions['A'].width = 18
        ws.column_dimensions['B'].width = 22
        for idx in range(3, 3 + len(years)):
            ws.column_dimensions[get_column_letter(idx)].width = 10
        ws.column_dimensions[get_column_letter(3 + len(years))].width = 10

    def _sheet_values_list_noyear(self, ws, snapshot: ProjectSnapshot, refs: Dict[Tuple[str, str], int] = None):
        for i, h in enumerate(["参数名称", "参数英文名"], 1):
            c = ws.cell(row=1, column=i, value=h)
            c.font = self.header_font_dark
            c.fill = self.header_fill_green
            c.alignment = self.center
            c.border = self.thin_border
        
        max_len = 0
        for cat in snapshot.categories:
            for p in cat.parameters:
                if p.is_list and not p.is_year_related:
                    max_len = max(max_len, p.list_length or len(p.values) or 0)
        
        col = 3
        for idx in range(1, max_len + 1):
            c = ws.cell(row=1, column=col, value=idx)
            c.font = self.header_font_dark
            c.fill = self.header_fill_green
            c.alignment = self.center
            c.border = self.thin_border
            col += 1
        
        c = ws.cell(row=1, column=col, value="单位")
        c.font = self.header_font_dark
        c.fill = self.header_fill_green
        c.alignment = self.center
        c.border = self.thin_border
        
        row = 2
        for cat in snapshot.categories:
            has_noyear_list_params = any(p.is_list and not p.is_year_related for p in cat.parameters)
            if not has_noyear_list_params:
                continue
            
            row = self._append_category_separator(ws, row, f"{cat.name} / {cat.name_en}", max_len + 3)
            params = sorted(cat.parameters, key=lambda p: (-p.sort_order, p.name or ''))
            for p in params:
                if not (p.is_list and not p.is_year_related):
                    continue
                if refs is not None:
                    refs[(cat.name_en, p.name_en)] = row
                ws.cell(row=row, column=1, value=p.name).font = self.font_normal
                ws.cell(row=row, column=2, value=p.name_en).font = self.font_normal
                for i in range(max_len):
                    val = p.values[i].value if i < len(p.values) else None
                    ws.cell(row=row, column=3 + i, value=(None if val in (None, "") else val)).font = self.font_normal
                ws.cell(row=row, column=3 + max_len, value=p.unit or "").font = self.font_normal
                for ccol in range(1, 4 + max_len):
                    ws.cell(row=row, column=ccol).border = self.thin_border
                row += 1
        
        ws.column_dimensions['A'].width = 18
        ws.column_dimensions['B'].width = 22
        for idx in range(3, 3 + max_len):
            ws.column_dimensions[get_column_letter(idx)].width = 10
        ws.column_dimensions[get_column_letter(3 + max_len)].width = 10

    def _update_param_definition_links(self, wb, snapshot: ProjectSnapshot, values_single_refs, values_year_refs, values_noyear_refs):
        for cat in snapshot.categories:
            sheet_name = self._safe_sheet_name(cat.name)
            if sheet_name in wb.sheetnames:
                ws = wb[sheet_name]
                for row in range(2, ws.max_row + 1):
                    param_name_en_cell = ws.cell(row=row, column=2)
                    if param_name_en_cell.value:
                        param_name_en = param_name_en_cell.value
                        key = (cat.name_en, param_name_en)
                        is_list_cell = ws.cell(row=row, column=5)
                        is_year_related_cell = ws.cell(row=row, column=6)
                        if is_list_cell.value == "否":
                            if key in values_single_refs:
                                target_row = values_single_refs[key]
                                name_cell = ws.cell(row=row, column=1)
                                name_cell.hyperlink = f"#值_单值!A{target_row}"
                                name_cell.style = "Hyperlink"
                        elif is_year_related_cell.value == "是":
                            if key in values_year_refs:
                                target_row = values_year_refs[key]
                                name_cell = ws.cell(row=row, column=1)
                                name_cell.hyperlink = f"#值_列表_年份!A{target_row}"
                                name_cell.style = "Hyperlink"
                        else:
                            if key in values_noyear_refs:
                                target_row = values_noyear_refs[key]
                                name_cell = ws.cell(row=row, column=1)
                                name_cell.hyperlink = f"#值_列表_非年份!A{target_row}"
                                name_cell.style = "Hyperlink"

    def _safe_sheet_name(self, name: str) -> str:
        name = (name or "").strip() or "Sheet"
        invalid = set('[]:*?/\\')
        safe = ''.join(ch for ch in name if ch not in invalid)
        return safe[:31]


class ExcelImportStrategy(ImportStrategy):
    """从 Excel 文件恢复项目快照"""

    @classmethod
    def get_format_info(cls) -> Dict[str, Any]:
        """获取 Excel 导入格式信息"""
        return {
            "format": "excel",
            "name": "富格式Excel文件",
            "extensions": [".xlsx"],
            "description": "多sheet、超链接、隐藏元数据的Excel文件"
        }

    def import_snapshot(self, input_path: Path) -> ProjectSnapshot:
        wb = load_workbook(str(input_path))
        
        # 解析项目概览
        if "项目概览" not in wb.sheetnames:
            raise ValueError("Excel文件缺少'项目概览'工作表")
        
        overview = wb["项目概览"]
        project_info = self._parse_project_overview(overview)
        
        # 解析参数目录
        if "参数目录" in wb.sheetnames:
            catalog = wb["参数目录"]
            categories_info = self._parse_catalog(catalog)
            categories_info = self._supplement_empty_categories_from_sheets(wb, categories_info)
        else:
            categories_info = self._infer_categories_from_sheets(wb)
        
        # 从各个分类工作表中解析详细的参数定义
        categories_info = self._parse_detailed_parameters_from_sheets(wb, categories_info)
        
        # 解析参数值
        values_data = self._parse_values_from_sheets(wb, project_info)
        
        # 构建 ProjectSnapshot
        categories = []
        for cat_info in categories_info:
            parameters = []
            for param_info in cat_info["parameters"]:
                param_key = f"{cat_info['name_en']}.{param_info['name_en']}"
                values = []
                if param_key in values_data:
                    for value_info in values_data[param_key]:
                        values.append(ParameterValueSnapshot(
                            index=value_info.get("list_index"),
                            value=value_info["value"],
                        ))
                else:
                    # 如果没有值，对于单值参数创建一个空值
                    # 对于列表参数，保持空列表（不添加任何值）
                    if not param_info["is_list"]:
                        values.append(ParameterValueSnapshot(index=None, value=None))
                
                # 计算 list_length
                list_length = None
                if param_info["is_list"]:
                    if param_info["is_year_related"]:
                        # 年份相关列表参数，list_length 等于时间范围
                        list_length = project_info["time_horizon"]
                    else:
                        # 非年份列表参数，list_length 等于值的数量
                        list_length = len(values) if values else None
                
                parameters.append(ParameterSnapshot(
                    name=param_info["name"],
                    name_en=param_info["name_en"],
                    param_type=param_info["param_type"],
                    unit=param_info.get("unit"),
                    description=param_info.get("description"),
                    is_list=param_info["is_list"],
                    is_year_related=param_info["is_year_related"],
                    list_length=list_length,
                    sort_order=param_info.get("sort_order", 0),
                    values=values,
                ))
            
            categories.append(CategorySnapshot(
                name=cat_info["name"],
                name_en=cat_info["name_en"],
                description=cat_info.get("description"),
                sort_order=cat_info.get("sort_order", 0),
                parameters=parameters,
            ))
        
        return ProjectSnapshot(
            metadata=ProjectMetadata(
                name=project_info["name"],
                name_en=project_info["name_en"],
                description=project_info.get("description"),
                time_horizon=project_info["time_horizon"],
                start_year=project_info["start_year"],
                year_step=project_info["year_step"],
                end_year=project_info["end_year"],
            ),
            categories=categories,
        )

    def get_diff_ignore_fields(self) -> List[str]:
        """Excel 格式需要屏蔽的字段：sort_order 和 list_length（Excel 文件中不包含这些字段）"""
        return ["category.sort_order", "parameter.sort_order", "parameter.list_length"]

    def _parse_project_overview(self, ws) -> Dict[str, Any]:
        """解析项目概览工作表"""
        info = {
            "name": None,
            "name_en": None,
            "description": None,
            "time_horizon": None,
            "start_year": None,
            "year_step": None,
            "end_year": None,
        }
        
        for row in ws.iter_rows(min_row=1, max_row=30, values_only=True):
            if len(row) < 2:
                continue
            k, v = row[0], row[1]
            if not k:
                continue
            
            if k == "项目名称":
                info["name"] = v
            elif k == "项目英文名":
                info["name_en"] = v
            elif k == "项目描述":
                info["description"] = v
            elif k == "时间长度":
                try:
                    info["time_horizon"] = int(str(v).split()[0])
                except Exception:
                    info["time_horizon"] = int(v)
            elif k == "起始年份":
                info["start_year"] = int(v)
            elif k == "年份步长":
                info["year_step"] = int(v)
            elif k == "结束年份":
                info["end_year"] = int(v)
        
        missing = [k for k, v in info.items() if v is None]
        if missing:
            raise ValueError(f"项目概览缺少字段: {missing}")
        
        return info

    def _parse_catalog(self, ws) -> List[Dict[str, Any]]:
        """解析参数目录工作表"""
        categories = {}
        
        header_row = None
        for row in range(1, ws.max_row + 1):
            if ws.cell(row=row, column=1).value == "分类":
                header_row = row
                break
        
        if not header_row:
            raise ValueError("参数目录工作表格式错误：找不到表头")
        
        for row in range(header_row + 1, ws.max_row + 1):
            cat_name = ws.cell(row=row, column=1).value
            cat_name_en = ws.cell(row=row, column=2).value
            param_name = ws.cell(row=row, column=3).value
            param_name_en = ws.cell(row=row, column=4).value
            param_desc = ws.cell(row=row, column=5).value
            
            if not cat_name or not cat_name_en or not param_name or not param_name_en:
                continue
            
            if cat_name_en not in categories:
                categories[cat_name_en] = {
                    "name": cat_name,
                    "name_en": cat_name_en,
                    "description": None,
                    "parameters": []
                }
            
            categories[cat_name_en]["parameters"].append({
                "name": param_name,
                "name_en": param_name_en,
                "description": param_desc,
                "param_type": "string",
                "unit": None,
                "is_list": False,
                "is_year_related": False,
                "list_length": None,
                "sort_order": 0,
            })
        
        return list(categories.values())

    def _supplement_empty_categories_from_sheets(self, wb, existing_categories: List[Dict[str, Any]]) -> List[Dict[str, Any]]:
        """从分类工作表中补充空分类信息"""
        existing_category_map = {cat["name"]: cat for cat in existing_categories}
        
        for sheet_name in wb.sheetnames:
            if sheet_name in ["项目概览", "参数目录", "值_单值", "值_列表_年份", "值_列表_非年份"]:
                continue
            
            if sheet_name not in existing_category_map:
                ws = wb[sheet_name]
                title = ws.cell(row=1, column=1).value
                if not title or not isinstance(title, str):
                    continue
                
                if "(" in title and ")" in title:
                    try:
                        cat_name = title.split(" (")[0].strip()
                        cat_name_en = title.split(" (")[1][:-1].strip()
                    except Exception:
                        cat_name = sheet_name
                        cat_name_en = sheet_name
                else:
                    cat_name = sheet_name
                    cat_name_en = sheet_name
                
                desc_cell = ws.cell(row=2, column=1).value
                description = None
                if desc_cell and isinstance(desc_cell, str) and desc_cell.startswith("描述:"):
                    description = desc_cell[3:].strip()
                
                existing_category_map[cat_name] = {
                    "name": cat_name,
                    "name_en": cat_name_en,
                    "description": description,
                    "parameters": []
                }
        
        return list(existing_category_map.values())

    def _infer_categories_from_sheets(self, wb) -> List[Dict[str, Any]]:
        """从工作表推断分类信息"""
        categories = {}
        
        for sheet_name in wb.sheetnames:
            if sheet_name in ["项目概览", "参数目录", "值_单值", "值_列表_年份", "值_列表_非年份"]:
                continue
            
            ws = wb[sheet_name]
            title = ws.cell(row=1, column=1).value
            if not title or not isinstance(title, str):
                continue
            
            if "(" in title and ")" in title:
                try:
                    cat_name = title.split(" (")[0].strip()
                    cat_name_en = title.split(" (")[1][:-1].strip()
                except Exception:
                    cat_name = sheet_name
                    cat_name_en = sheet_name
            else:
                cat_name = sheet_name
                cat_name_en = sheet_name
            
            desc_cell = ws.cell(row=2, column=1).value
            description = None
            if desc_cell and isinstance(desc_cell, str) and desc_cell.startswith("描述:"):
                description = desc_cell[3:].strip()
            
            parameters = self._parse_parameters_from_sheet(ws)
            
            categories[cat_name] = {
                "name": cat_name,
                "name_en": cat_name_en,
                "description": description,
                "parameters": parameters
            }
        
        return list(categories.values())

    def _parse_detailed_parameters_from_sheets(self, wb, categories_info: List[Dict[str, Any]]) -> List[Dict[str, Any]]:
        """从各个分类工作表中解析详细的参数定义"""
        category_map = {cat["name"]: cat for cat in categories_info}
        
        for sheet_name in wb.sheetnames:
            if sheet_name in ["项目概览", "参数目录", "值_单值", "值_列表_年份", "值_列表_非年份"]:
                continue
            
            if sheet_name in category_map:
                ws = wb[sheet_name]
                description = self._parse_category_description(ws)
                if description:
                    category_map[sheet_name]["description"] = description
                
                parameters = self._parse_parameters_from_sheet(ws)
                if parameters:
                    category_map[sheet_name]["parameters"] = parameters
        
        return list(category_map.values())

    def _parse_category_description(self, ws) -> str | None:
        """从分类工作表中解析分类描述"""
        desc_cell = ws.cell(row=2, column=1).value
        if desc_cell and isinstance(desc_cell, str) and desc_cell.startswith("描述:"):
            return desc_cell[3:].strip()
        return None

    def _parse_parameters_from_sheet(self, ws) -> List[Dict[str, Any]]:
        """从工作表解析参数信息"""
        parameters = []
        
        header_row = None
        for row in range(1, ws.max_row + 1):
            if ws.cell(row=row, column=1).value == "参数名称":
                header_row = row
                break
        
        if not header_row:
            return parameters
        
        for row in range(header_row + 1, ws.max_row + 1):
            name = ws.cell(row=row, column=1).value
            name_en = ws.cell(row=row, column=2).value
            param_type = ws.cell(row=row, column=3).value
            unit = ws.cell(row=row, column=4).value
            is_list = ws.cell(row=row, column=5).value
            is_year_related = ws.cell(row=row, column=6).value
            description = ws.cell(row=row, column=7).value
            
            if not name or not name_en:
                continue
            
            parameters.append({
                "name": name,
                "name_en": name_en,
                "param_type": param_type or "string",
                "unit": unit,
                "description": description,
                "is_list": is_list == "是",
                "is_year_related": is_year_related == "是",
                "list_length": None,
                "sort_order": 0,
            })
        
        return parameters

    def _parse_values_from_sheets(self, wb, project_info: Dict[str, Any]) -> Dict[str, List[Dict[str, Any]]]:
        """从值工作表解析参数值"""
        values = {}
        
        if "值_单值" in wb.sheetnames:
            single_values = self._parse_single_values(wb["值_单值"])
            values.update(single_values)
        
        if "值_列表_年份" in wb.sheetnames:
            year_values = self._parse_year_list_values(wb["值_列表_年份"], project_info)
            values.update(year_values)
        
        if "值_列表_非年份" in wb.sheetnames:
            noyear_values = self._parse_noyear_list_values(wb["值_列表_非年份"])
            values.update(noyear_values)
        
        return values

    def _parse_single_values(self, ws) -> Dict[str, List[Dict[str, Any]]]:
        """解析单值参数"""
        values = {}
        
        header_row = None
        for row in range(1, ws.max_row + 1):
            if ws.cell(row=row, column=1).value == "参数名称":
                header_row = row
                break
        
        if not header_row:
            return values
        
        for row in range(header_row + 1, ws.max_row + 1):
            param_name_en = ws.cell(row=row, column=2).value
            value = ws.cell(row=row, column=3).value
            
            if not param_name_en or value is None:
                continue
            
            category_name_en = self._infer_category_from_single_values(ws, row)
            param_key = f"{category_name_en}.{param_name_en}"
            
            if param_key not in values:
                values[param_key] = []
            
            values[param_key].append({
                "value": str(value),
                "list_index": None
            })
        
        return values

    def _infer_category_from_single_values(self, ws, row: int) -> str:
        """从单值工作表中推断分类名"""
        for r in range(row, 0, -1):
            cell_value = ws.cell(row=r, column=1).value
            if cell_value and isinstance(cell_value, str) and cell_value.startswith("【"):
                content = cell_value[1:-1]
                if " / " in content:
                    return content.split(" / ")[1].strip()
        return "unknown"

    def _parse_year_list_values(self, ws, project_info: Dict[str, Any]) -> Dict[str, List[Dict[str, Any]]]:
        """解析年份相关列表参数"""
        values = {}
        
        years = [
            project_info["start_year"] + i * project_info["year_step"]
            for i in range(project_info["time_horizon"])
        ]
        
        header_row = None
        for row in range(1, ws.max_row + 1):
            if ws.cell(row=row, column=1).value == "参数名称":
                header_row = row
                break
        
        if not header_row:
            return values
        
        for row in range(header_row + 1, ws.max_row + 1):
            param_name_en = ws.cell(row=row, column=2).value
            
            if not param_name_en:
                continue
            
            category_name_en = self._infer_category_from_single_values(ws, row)
            param_key = f"{category_name_en}.{param_name_en}"
            
            if param_key not in values:
                values[param_key] = []
            
            for i, year in enumerate(years):
                value = ws.cell(row=row, column=3 + i).value
                if value is not None:
                    values[param_key].append({
                        "value": str(value),
                        "list_index": i
                    })
        
        return values

    def _parse_noyear_list_values(self, ws) -> Dict[str, List[Dict[str, Any]]]:
        """解析非年份列表参数"""
        values = {}
        
        header_row = None
        for row in range(1, ws.max_row + 1):
            if ws.cell(row=row, column=1).value == "参数名称":
                header_row = row
                break
        
        if not header_row:
            return values
        
        unit_col = None
        for c in range(1, ws.max_column + 1):
            if ws.cell(row=header_row, column=c).value == "单位":
                unit_col = c
                break
        
        for row in range(header_row + 1, ws.max_row + 1):
            param_name_en = ws.cell(row=row, column=2).value
            
            if not param_name_en:
                continue
            
            category_name_en = self._infer_category_from_single_values(ws, row)
            param_key = f"{category_name_en}.{param_name_en}"
            
            if param_key not in values:
                values[param_key] = []
            
            col = 3
            index = 0
            while True:
                if unit_col and col == unit_col:
                    col += 1
                    continue
                
                value = ws.cell(row=row, column=col).value
                if value is None:
                    break
                
                values[param_key].append({
                    "value": str(value),
                    "list_index": index
                })
                
                col += 1
                index += 1
        
        return values

